﻿using Oracle.DataAccess.Client;
using System;
using System.Data;
using System.Collections.Generic;
using System.Configuration;
using Common;
using System.Linq;
using Platform.Configuration;

namespace YXYLCommon
{
    public class DBHelp
    {
        static string connString = ConfigHelper.PlatformConnString;

       
       // static string connString = ConfigurationManager.ConnectionStrings["PlatformConnString"].ToString();
        public static List<CheckDataJH> SelectJH(string time)
        {
            string sql = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and  S_beizhu like '%非托:%' and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet ds= SelectData(sql);
            List<CheckDataJH> list = new List<Common.CheckDataJH>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                CheckDataJH model = new CheckDataJH();
                model.id = ds.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                model.name = ds.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                model.money = ds.Tables[0].Rows[i]["N_DAIFANGJE"].ToString();
                list.Add(model);
            }
            return list;
        }
        public static List<CheckDataCW> SelectCW(string time)
        {
            string sql = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and S_beizhu like '%转帐:%'  and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet ds = SelectData(sql);
            List<CheckDataCW> list = new List<CheckDataCW>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                CheckDataCW model = new CheckDataCW();
                model.id = ds.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                model.name = ds.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                model.money = ds.Tables[0].Rows[i]["N_JIEFANGJE"].ToString();
                list.Add(model);
            }
            return list;
        }
        public static List<CheckDataJH> SelectJHId(string time)
        {
            string sqlJH = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and  S_beizhu like '%非托:%' and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsJH = SelectData(sqlJH);
            string sqlCW = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and S_beizhu like '%转帐:%'  and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsCW = SelectData(sqlJH);
            List<CheckDataJH> list = new List<CheckDataJH>();
            for (int i = 0; i < dsJH.Tables[0].Rows.Count; i++)
            {
                CheckDataJH model = new CheckDataJH();
                string idJH = dsJH.Tables[0].Rows[i]["s_kehudm"].ToString();
                
                if (dsCW.Tables[0].Rows.Cast<DataRow>().Any(x => x["s_kuhudm"].ToString() != idJH))
                {
                    model.id = dsJH.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                    model.name = dsJH.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                    model.money = dsJH.Tables[0].Rows[i]["N_DAIFANGJE"].ToString();
                    list.Add(model);
                }

            }
            return list;
        }
        public static List<CheckDataCW> SelectCWId(string time)
        {
            string sqlJH = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and  S_beizhu like '%非托:%' and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsJH = SelectData(sqlJH);
            string sqlCW = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and S_beizhu like '%转帐:%'  and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsCW = SelectData(sqlJH);
            List<CheckDataCW> list = new List<CheckDataCW>();
            for (int i = 0; i < dsCW.Tables[0].Rows.Count; i++)
            {
                CheckDataCW model = new CheckDataCW();
                string idCW = dsCW.Tables[0].Rows[i]["s_kehudm"].ToString();

                
                if (dsJH.Tables[0].Rows.Cast<DataRow>().Any(x => x["s_kuhudm"].ToString() != idCW))
                {
                    model.id = dsCW.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                    model.name = dsCW.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                    model.money = dsCW.Tables[0].Rows[i]["N_JIEFANGJE"].ToString();
                    list.Add(model);
                }

            }
            return list;
        }
        public static List<CheckDataJH> SelectJHNum(string time)
        {
            string sqlJH = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and  S_beizhu like '%非托:%' and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsJH = SelectData(sqlJH);
            string sqlCW = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and S_beizhu like '%转帐:%'  and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsCW = SelectData(sqlJH);
            List<CheckDataJH> list = new List<CheckDataJH>();
            for (int i = 0; i < dsJH.Tables[0].Rows.Count; i++)
            {
                CheckDataJH model = new CheckDataJH();
                string idJH = dsJH.Tables[0].Rows[i]["s_kehudm"].ToString();
                string numJH = dsJH.Tables[0].Rows[i]["N_DAIFANGJE"].ToString();

                if (dsCW.Tables[0].Rows.Cast<DataRow>().Any(x => x["s_kuhudm"].ToString() == idJH)&& dsCW.Tables[0].Rows.Cast<DataRow>().Any(x => x["N_JIEFANGJE"].ToString() != numJH))
                {
                    model.id = dsJH.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                    model.name = dsJH.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                    model.money = dsJH.Tables[0].Rows[i]["N_DAIFANGJE"].ToString();
                    list.Add(model);
                }

            }
            return list;
        }
        public static List<CheckDataCW> SelectCWNum(string time)
        {
            string sqlJH = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and  S_beizhu like '%非托:%' and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsJH = SelectData(sqlJH);
            string sqlCW = "select * from YS_ZW_LICHENGCWJKB t where s_gongzuony ='" + time + "' and S_beizhu like '%转帐:%'  and t.s_Zhaiyao like '%预收%' order by  t.s_Zhaiyao";
            DataSet dsCW = SelectData(sqlJH);
            List<CheckDataCW> list = new List<CheckDataCW>();
            for (int i = 0; i < dsCW.Tables[0].Rows.Count; i++)
            {
                CheckDataCW model = new CheckDataCW();
                string idCW = dsCW.Tables[0].Rows[i]["s_kehudm"].ToString();
                string numCW = dsCW.Tables[0].Rows[i]["N_JIEFANGJE"].ToString();

                if (dsJH.Tables[0].Rows.Cast<DataRow>().Any(x => x["s_kuhudm"].ToString() != idCW) && dsJH.Tables[0].Rows.Cast<DataRow>().Any(x => x["N_DAIFANGJE"].ToString() != numCW))
                {
                    model.id = dsCW.Tables[0].Rows[i]["S_KEHUDM"].ToString();
                    model.name = dsCW.Tables[0].Rows[i]["S_KEHUMC"].ToString();
                    model.money = dsCW.Tables[0].Rows[i]["N_JIEFANGJE"].ToString();
                    list.Add(model);
                }

            }
            return list;
        }

        /// <summary>
        /// 查询表数据（单表）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataSet SelectData(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                using (OracleConnection conn = new OracleConnection(connString))
                {
                    //conn.Open();
                    OracleCommand cmd = new OracleCommand(sql, conn);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch (Exception e)
            {
                Console.Write(e);
            }
            return null;
        }

        //增改删
        /// <summary>
        /// 执行sql 语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回影响的行数</returns>
        public static Boolean AddUpDelData(string sql)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connString))
                {
                    conn.Open();
                    OracleCommand cmd = new OracleCommand(sql, conn);
                    int row = cmd.ExecuteNonQuery();
                    conn.Close();
                    if (row > 0)
                    {
                        return true;
                    }
                }
            }
            catch (Exception e)
            {
                Console.Write(e);
            }
            return false;
        }

        public static Boolean ExeTransaction(List<string> sqlText)

        {

            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                OracleTransaction tran = conn.BeginTransaction();
                try
                {

                    OracleCommand cmd = new OracleCommand();

                    cmd.Transaction = tran;

                    cmd.Connection = conn;

                    foreach (string item in sqlText)
                    {
                        cmd.CommandText = item;

                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();

                    return true;

                }

                catch (Exception et)

                {
                    tran.Rollback();
                    return false;
                }

                finally
                {
                    conn.Close();
                }

            }

        }

    }
}